#
# Functions to work with a database
#

import sqlite3
import os

VERY_KNOWN_WORDS = [
    "that", "this", "those", "what",
    "mine", "your", "it's", "don't",
]


def filter_out_known_words(db_file, words_count):
    all_words = words_count.keys()
    all_words_sql = ', '.join(['?' for w in xrange(len(all_words))])

    sql = """
        SELECT
            word
        FROM
            known_words
        WHERE word IN ({})
    """.format(all_words_sql)

    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()
    cursor.execute(sql, all_words)

    for record in cursor.fetchall():
        known_word = record[0]
        del words_count[known_word]

    return words_count


def create_new_db(db_file, skip_if_exists=True):
    if skip_if_exists and os.path.isfile(db_file):
        return

    sql = "CREATE TABLE known_words (word TEXT PRIMARY KEY)"
    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()

    add_to_known_words(db_file, VERY_KNOWN_WORDS)


def add_to_known_words(db_file, words):
    sql = "INSERT OR IGNORE INTO known_words (word) VALUES (?)"
    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()
    cursor.executemany(sql, [(w,) for w in words])
    conn.commit()


def delete_from_known_words(db_file, words):
    sql = "DELETE FROM known_words WHERE word=?"
    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()
    cursor.executemany(sql, [(w,) for w in words])
    conn.commit()
